USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAds_GetByID]    Script Date: 05/25/2011 06:55:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [classified].[ClassifiedAds_GetByID]
	@ResourceID bigint,
	@CategoryID bigint,
	@AdID bigint
AS
BEGIN

	SET NOCOUNT ON;
	
	IF @CategoryID = 0
	BEGIN
		SELECT @CategoryID = CategoryID FROM classified.ClassifiedAds WHERE ResourceID = @ResourceID AND AdID = @AdID
	END
	
	SELECT * FROM classified.ClassifiedAdsCustomAttributeValues
	WHERE ResourceID = @ResourceID AND AdID = @AdID
	
	Select AttributeID, AttributeValue FROM classified.ClassifiedAdsAttributeValues
	WHERE ResourceID = @ResourceID AND AdID = @AdID
	
	SELECT * FROM classified.ClassifiedAttributeValues
	WHERE CategoryID = @CategoryID
	
	SELECT * FROM classified.ClassifiedAttributes
	WHERE CategoryID = @CategoryID
	ORDER BY AttributeID, SortOrder
	
	SELECT 
      [ResourceID]
      ,[CategoryID]
      ,[PostTime]
      ,[ExpiryTime]
      ,PostTime
      ,[Searching]
      ,[Title]
      ,[AdText]
      ,[TextFormat]
      ,CAST(Location.Lat as float) as latitude
      ,CAST(Location.Long as float) as longitude
      ,[PriceType]
      ,[Price]
      ,[ContactPhone]
      ,[ContactEmail]
      ,[ContactUrl]
      ,[UserID]
      ,[UserComputerID]
      ,[UserIP]
      ,[Status]
      ,[Description]
	FROM classified.ClassifiedAds
	WHERE ResourceID = @ResourceID AND AdID = @AdID
	
	IF (@@ROWCOUNT=0) -- search in expired
		BEGIN
			SELECT 
		  [ResourceID]
		  ,[CategoryID]
		  ,[PostTime]
		  ,[ExpiryTime]
		  ,PostTime
		  ,[Searching]
		  ,[Title]
		  ,[AdText]
		  ,[TextFormat]
		  ,CAST(Location.Lat as float) as latitude
		  ,CAST(Location.Long as float) as longitude
		  ,[PriceType]
		  ,[Price]
		  ,'Expired' as ContactPhone
		  ,'Expired' as ContactEmail
		  ,'Expired' as ContactUrl
		  ,[UserID]
		  ,[UserComputerID]
		  ,[UserIP]
		FROM classified.ExpiredClassifiedAds
		WHERE ResourceID = @ResourceID AND AdID = @AdID
	END
	
	SELECT AllowRenewal, AutoExpireAnonymousDays, AutoExpireNonAnonymousDays, Comments, AdApprove, CurrencyID
	FROM classified.ClassifiedResources WHERE ResourceID = @ResourceID AND MasterResourceID IS NULL
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		SELECT AllowRenewal, AutoExpireAnonymousDays, AutoExpireNonAnonymousDays, Comments, AdApprove, CurrencyID
		FROM classified.ClassifiedResources WHERE ResourceID IN (SELECT MasterResourceID FROM classified.ClassifiedResources WHERE ResourceID = @ResourceID)
	END
	
	SELECT * FROM classified.ClassifiedCurrency
	
END





GO


